﻿using Fasterflect;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace HobSoft.Utility.SqlGateway
{
    using Extensions;

    /// <summary>
    /// Sql查询代理工具类
    /// </summary>
    //2015.04.24 加入日志记录
    public class SqlGateway : IDisposable
    {
        private log4net.ILog log = log4net.LogManager.GetLogger(typeof(SqlGateway));

        private static Lazy<ConcurrentDictionary<Type, ConcurrentDictionary<string, PropertyInfo>>> poolCommandType
                            = new Lazy<ConcurrentDictionary<Type, ConcurrentDictionary<string, PropertyInfo>>>(
                                () => new ConcurrentDictionary<Type, ConcurrentDictionary<string, PropertyInfo>>());

        private readonly DbFactory provider;
        private readonly string connectionString;
        private DbConnection connection;
        private DbTransaction transaction;

        private ConcurrentDictionary<Type, ConcurrentDictionary<string, PropertyInfo>> commandTypePool
        {
            get { return poolCommandType.Value; }
        }

        #region private

        private ConcurrentDictionary<string, PropertyInfo> GetTypeMapping(Type mappingType)
        {
            ConcurrentDictionary<string, PropertyInfo> fieldMapping = null;
            if (!commandTypePool.ContainsKey(mappingType))
            {
                commandTypePool.TryAdd(mappingType, new ConcurrentDictionary<string, PropertyInfo>());
            }
            fieldMapping = commandTypePool[mappingType];
            return fieldMapping;
        }

        private void WriteError(string command, Exception ex)
        {
            if (log != null && log.IsErrorEnabled)
            {
                log.ErrorFormat("{0}, {1}", command, connection.ConnectionString);
                log.Error(command, ex);
            }
        }

        #endregion private

        #region cort

        private SqlGateway(string connectionString, string providerName)
            : this(connectionString, providerName, false)
        {
        }

        private SqlGateway(string connectionString, string providerName, bool openTran)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(connectionString, string.Format("need config connection section【{0}】", connectionString));

            if (string.IsNullOrEmpty(providerName))
                provider = new DbFactory();
            else
                provider = new DbFactory(providerName);

            this.connectionString = connectionString;
            if (openTran)
            {
                HasUseTransaction = true;
                connection = CreateConnection();
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                transaction = connection.BeginTransaction();
            }
        }

        #endregion cort

        #region create and close

        /// <summary>
        /// 根据配置节点创建SqlGateway
        /// </summary>
        /// <param name="sectionName">配置节名称</param>
        /// <returns></returns>
        public static SqlGateway Create(string sectionName)
        {
            return Create(sectionName, false);
        }

        /// <summary>
        /// 根据配置节点创建SqlGateway
        /// </summary>
        /// <param name="sectionName">配置节名称</param>
        /// <param name="openTran">立即开启事务</param>
        /// <returns></returns>
        public static SqlGateway Create(string sectionName, bool openTran)
        {
            var connectionConfig = System.Configuration.ConfigurationManager.ConnectionStrings[sectionName];
            var connectionString = connectionConfig.ConnectionString;
            var providerName = connectionConfig.ProviderName;
            var gateway = new SqlGateway(connectionString, providerName, openTran);
            gateway.Commited = false;
            return gateway;
        }

        /// <summary>
        /// 根据连接字符串和提供程序创建SqlGateway
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="providerName">工厂提供程序名称</param>
        /// <returns></returns>
        public static SqlGateway Create(string connectionString, string providerName)
        {
            var gateway = new SqlGateway(connectionString, providerName, false);
            gateway.Commited = false;
            return gateway;
        }

        /// <summary>
        /// 根据连接字符串和提供程序创建SqlGateway
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="providerName">工厂提供程序名称</param>
        /// <param name="openTran">立即开启事务</param>
        /// <returns></returns>
        public static SqlGateway Create(string connectionString, string providerName, bool openTran)
        {
            var gateway = new SqlGateway(connectionString, providerName, openTran);
            gateway.Commited = false;
            return gateway;
        }

        protected bool IsClose = false;

        public void Close()
        {
            if (IsClose) return;
            IsClose = true;
            Commit();
            if (connection != null && connection.State != ConnectionState.Closed)
            {
                connection.Close();
                if (transaction != null)
                    transaction.Dispose();
                connection.Dispose();
                connection = null;
            }
        }

        #endregion create and close

        #region provider create db object

        protected DbCommand CreateCommand(string query, CommandType commandType)
        {
            if (connection == null)
                connection = CreateConnection();

            var command = provider.CreateCommand();
            if (HasUseTransaction)
            {
                if (transaction == null)
                    transaction = connection.BeginTransaction();
                command.Transaction = transaction;
            }
            command.CommandText = query;
            command.CommandType = commandType;
            command.Connection = connection;

            if (connection.State != ConnectionState.Open)
                connection.Open();
            return command;
        }

        protected DbConnection CreateConnection()
        {
            var conn = provider.CreateConnection();
            conn.ConnectionString = connectionString;
            return conn;
        }

        #endregion provider create db object

        #region transaction method

        protected bool HasUseTransaction = false;
        protected object tranLock = new object();

        public bool Commited { get; protected set; }

        public void Commit()
        {
            if (transaction == null || Commited) return;
            lock (tranLock)
            {
                if (transaction != null && !Commited && HasUseTransaction)
                    transaction.Commit();
                Commited = true;
                transaction = null;
                HasUseTransaction = false;
            }
        }

        public void Rollback()
        {
            if (transaction == null || Commited) return;
            lock (tranLock)
            {
                if (transaction != null && !Commited && HasUseTransaction)
                    transaction.Rollback();
                Commited = true;
                transaction = null;
                HasUseTransaction = false;
            }
        }

        #endregion transaction method

        #region throw Exception

        protected void ThrowIfClose()
        {
            if (IsClose) throw new Exception("gateway is closed.");
        }

        #endregion throw Exception

        #region protected method

        protected void AttachCommandParameters(DbCommand command, params DbParameter[] paramters)
        {
            if (paramters == null) return;
            foreach (var param in paramters)
            {
                if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input)
                    && param.Value == null)
                {
                    param.Value = DBNull.Value;
                }
                command.Parameters.Add(param);
            }
        }

        protected Nullable<T> CastDbValue<T>(object value) where T : struct
        {
            if (value == null || value == DBNull.Value) return null;
            Nullable<T> result = null;
            try
            {
                if (value is T)
                    result = (T)value;
                else
                    result = (T)Convert.ChangeType(value, typeof(T));
            }
            catch
            {
            }
            return result;
        }

        #endregion protected method

        #region create db paramter

        public DbParameter CreateParamter()
        {
            return provider.CreateParameter();
        }

        public DbParameter CreateParamter(string name, object value)
        {
            var paramter = provider.CreateParameter();
            paramter.ParameterName = name;
            paramter.Value = value;
            return paramter;
        }

        #endregion create db paramter

        #region execute command

        #region ExecuteNonQuery

        /// <summary>
        /// 执行Sql命令
        /// </summary>
        /// <param name="command">Sql命令</param>
        /// <param name="paramters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string command, params DbParameter[] paramters)
        {
            return ExecuteNonQuery(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">参数</param>
        /// <returns></returns>
        public int ExecuteProcNonQuery(string procName, params DbParameter[] paramters)
        {
            return ExecuteNonQuery(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 执行Sql命令
        /// </summary>
        /// <param name="command">命令名称</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string command, CommandType commandType, params DbParameter[] paramters)
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);
            try
            {
                return dbCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            finally
            {
            }
        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        /// <summary>
        /// 查询Sql命令，返回第一行，第一列数据
        /// </summary>
        /// <typeparam name="T">结果类型</typeparam>
        /// <param name="command">SQL命令</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public Nullable<T> ExecuteScalar<T>(string command, params DbParameter[] paramters) where T : struct
        {
            return ExecuteScalar<T>(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 查询存储过程，返回第一行，第一列数据
        /// </summary>
        /// <typeparam name="T">结果类型</typeparam>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public Nullable<T> ExecuteProcScalar<T>(string procName, params DbParameter[] paramters) where T : struct
        {
            return ExecuteScalar<T>(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 查询Sql命令，返回第一行，第一列数据
        /// </summary>
        /// <typeparam name="T">结果类型</typeparam>
        /// <param name="command">SQL命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public Nullable<T> ExecuteScalar<T>(string command, CommandType commandType, params DbParameter[] paramters) where T : struct
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);
            try
            {
                var result = dbCommand.ExecuteScalar();
                return CastDbValue<T>(result);
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
        }

        /// <summary>
        /// 查询Sql命令，返回第一行，第一列字符串数据
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public string ExecuteScalarString(string command, params DbParameter[] paramters)
        {
            return ExecuteScalarString(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 查询存储过程，返回第一行，第一列字符串数据
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public string ExecuteProcScalarString(string procName, params DbParameter[] paramters)
        {
            return ExecuteScalarString(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 查询Sql命令，返回第一行，第一列字符串数据
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public string ExecuteScalarString(string command, CommandType commandType, params DbParameter[] paramters)
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);
            try
            {
                var result = dbCommand.ExecuteScalar();
                if (result == null || result == DBNull.Value) return null;
                return result.ToString();
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
        }

        #endregion ExecuteScalar

        #region ExecuteAllScalar

        /// <summary>
        /// 执行查询命令，返回第一列数据的结果集合
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="command">Sql命令</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public IList<T?> ExecuteAllScalar<T>(string command, params DbParameter[] paramters) where T : struct
        {
            return ExecuteAllScalar<T>(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 执行查询存储过程，返回第一列数据的结果集合
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="paramters">查询参数</param>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public IList<T?> ExecuteProcAllScalar<T>(string procName, params DbParameter[] paramters) where T : struct
        {
            return ExecuteAllScalar<T>(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 执行查询命令，返回第一列数据的结果集合
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="command">查询命令</param>
        /// <param name="commandType">查询类型</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public IList<T?> ExecuteAllScalar<T>(string command, CommandType commandType, params DbParameter[] paramters) where T : struct
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);

            List<T?> result = new List<T?>();
            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        object dbValue = reader.GetValue(0);
                        result.Add(CastDbValue<T>(dbValue));
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 执行查询命令，返回第一列数据的结果字符串集合
        /// </summary>
        /// <param name="command">Sql命令</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public IList<string> ExecuteAllScalar(string command, params DbParameter[] paramters)
        {
            return ExecuteAllScalar(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 执行查询存储过程，返回第一列数据的结果字符串集合
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public IList<string> ExecuteProcAllScalar(string procName, params DbParameter[] paramters)
        {
            return ExecuteAllScalar(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 执行查询命令，返回第一列数据的结果字符串集合
        /// </summary>
        /// <param name="command">查询命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public IList<string> ExecuteAllScalar(string command, CommandType commandType, params DbParameter[] paramters)
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);

            List<string> result = new List<string>();
            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        object dbValue = reader.GetValue(0);
                        if (dbValue == null || dbValue == DBNull.Value)
                        {
                            result.Add(string.Empty);
                        }
                        else
                        {
                            result.Add(dbValue.ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return result;
        }

        #endregion ExecuteAllScalar

        #endregion execute command

        #region select dto object

        #region select dto list

        /// <summary>
        /// Sql查询命令，返回对象集合
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="command">Sql查询命令</param>
        /// <param name="paramters">传递的参数</param>
        /// <returns></returns>
        public IList<T> Select<T>(string command, params DbParameter[] paramters) where T : class, new()
        {
            return Select<T>(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 查询存储过程，返回对象集合
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">传递的参数</param>
        /// <returns></returns>
        public IList<T> SelectProc<T>(string procName, params DbParameter[] paramters) where T : class, new()
        {
            return Select<T>(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// Sql查询命令，返回对象集合
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="command">查询命令</param>
        /// <param name="commandType">查询类型</param>
        /// <param name="paramters">参数</param>
        /// <returns></returns>
        public IList<T> Select<T>(string command, CommandType commandType, params DbParameter[] paramters) where T : class, new()
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);

            List<T> result = new List<T>();
            Type type = typeof(T);
            var columnPropertyTable = GetTypeMapping(type);
            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = Activator.CreateInstance<T>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            object dbValue = reader.GetValue(i);
                            if (dbValue == null || dbValue == DBNull.Value) continue;
                            if (!columnPropertyTable.ContainsKey(name))
                            {
                                var propertyInfo = type.Property(name);
                                if (propertyInfo != null && propertyInfo.IsWritable())
                                    columnPropertyTable.TryAdd(name, propertyInfo);
                            }
                            if (!columnPropertyTable.ContainsKey(name)) continue;
                            var currentProperty = columnPropertyTable[name];

                            object dbDataValue;
                            if (reader.TryCast(i, currentProperty.PropertyType, out dbDataValue))
                                currentProperty.Set(item, dbDataValue);
                        }
                        result.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return result;
        }

        #endregion select dto list

        #region select sigle dto

        /// <summary>
        /// 执行Sql查询命令，返回第一行的单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="command">查询Sql命令</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public T SelectSingle<T>(string command, params DbParameter[] paramters) where T : class, new()
        {
            return SelectSingle<T>(command, CommandType.Text, paramters);
        }

        /// <summary>
        /// 执行Sql存储过程，返回第一行的单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public T SelectProcSingle<T>(string procName, params DbParameter[] paramters) where T : class, new()
        {
            return SelectSingle<T>(procName, CommandType.StoredProcedure, paramters);
        }

        /// <summary>
        /// 执行查询命令，返回第一行的单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="command">查询命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramters">查询参数</param>
        /// <returns></returns>
        public T SelectSingle<T>(string command, CommandType commandType, params DbParameter[] paramters) where T : class, new()
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);

            T result = null;
            Type type = typeof(T);
            var columnPropertyTable = GetTypeMapping(type);
            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result = Activator.CreateInstance<T>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            object dbValue = reader.GetValue(i);
                            if (dbValue == null || dbValue == DBNull.Value) continue;
                            if (!columnPropertyTable.ContainsKey(name))
                            {
                                var propertyInfo = type.Property(name);
                                if (propertyInfo != null && propertyInfo.IsWritable())
                                    columnPropertyTable.TryAdd(name, propertyInfo);
                            }

                            if (!columnPropertyTable.ContainsKey(name)) continue;
                            var currentProperty = columnPropertyTable[name];
                            object dbDataValue;
                            if (reader.TryCast(i, currentProperty.PropertyType, out dbDataValue))
                                currentProperty.Set(result, dbDataValue);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return result;
        }

        #endregion select sigle dto

        #endregion select dto object

        #region select dynamic object

        #region select dynamic list

        public IList<dynamic> Select(string command, params DbParameter[] paramters)
        {
            return Select(command, CommandType.Text, paramters);
        }

        public IList<dynamic> SelectProc(string procName, params DbParameter[] paramters)
        {
            return Select(procName, CommandType.StoredProcedure, paramters);
        }

        public IList<dynamic> Select(string command, CommandType commandType, params DbParameter[] paramters)
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);
            var results = new List<dynamic>();
            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        dynamic result = new System.Dynamic.ExpandoObject();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            object dbValue = reader.GetValue(i);
                            var dyObjDic = result as IDictionary<string, object>;
                            if (dbValue == null || dbValue == DBNull.Value)
                            {
                                dyObjDic[name] = null;
                                continue;
                            }
                            dyObjDic[name] = dbValue;
                        }
                        results.Add(result);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return results;
        }

        #endregion select dynamic list

        #region select single dynamic object

        public dynamic SelectSingle(string command, params DbParameter[] paramters)
        {
            return SelectSingle(command, CommandType.Text, paramters);
        }

        public dynamic SelectProcSingle(string procName, params DbParameter[] paramters)
        {
            return SelectSingle(procName, CommandType.StoredProcedure, paramters);
        }

        public dynamic SelectSingle(string command, CommandType commandType, params DbParameter[] paramters)
        {
            ThrowIfClose();
            var dbCommand = CreateCommand(command, commandType);
            AttachCommandParameters(dbCommand, paramters);

            try
            {
                using (var reader = dbCommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        dynamic result = new System.Dynamic.ExpandoObject();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            object dbValue = reader.GetValue(i);
                            var dyObjDic = result as IDictionary<string, object>;
                            if (dbValue == null || dbValue == DBNull.Value)
                            {
                                dyObjDic[name] = null;
                                continue;
                            }
                            dyObjDic[name] = dbValue;
                        }
                        return result;
                    }
                }
            }
            catch (Exception ex)
            {
                WriteError(command, ex);
                throw ex;
            }
            return null;
        }

        #endregion select single dynamic object

        #endregion select dynamic object

        #region dispose

        private bool isDispose = false;
        private object disposeSync = new object();

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected void Dispose(bool disposing)
        {
            if (disposing && !isDispose)
            {
                lock (disposeSync)
                {
                    if (disposing && !isDispose)
                    {
                        isDispose = true;
                        Close();
                    }
                }
            }
        }

        ~SqlGateway()
        {
            Dispose(false);
        }

        #endregion dispose
    }
}